package com.iweb.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
    static Connection conn = null;//表示数据库的连接
    //static Statement stat = null;//表示一个可以操作sql语句的对象
    static PreparedStatement preparedStatement = null;
    static ResultSet rest = null;

    /*
    获取数据库连接
     */
    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动类
            //获取数据库的连接
            conn = DriverManager.getConnection(
                    "jdbc:mysql://192.168.100.15:3306/mysql?characterEncoding=utf-8",
                    "root", "123456");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /*
    关闭资源
     */
    public static void closeResource() {
        try {
            if (rest != null)
                rest.close();
            if (preparedStatement != null)
                preparedStatement.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
    增删改
     */
    public static <E> int jdbcByUpdate(String sql, E... e) {
        System.out.println("=== 开始执行SQL更新 ===");
        System.out.println("SQL语句: " + sql);
        System.out.println("参数: ");
        for(int i = 0; i < e.length; i++) {
            System.out.println("参数" + (i+1) + ": " + e[i]);
        }
        int i = 0;
        try {
            getConnection();
            System.out.println("数据库连接成功");
            preparedStatement = conn.prepareStatement(sql);
            System.out.println("PreparedStatement创建成功");
            for (int j = 0; j < e.length; j++) {
                preparedStatement.setObject(j + 1, e[j]);
                System.out.println("设置参数" + (j+1) + "成功");
            }
            i = preparedStatement.executeUpdate();
            System.out.println("SQL执行成功，影响行数: " + i);
        } catch (SQLException exception) {
            System.out.println("SQL执行出错: ");
            exception.printStackTrace();
        } finally {
            closeResource();
            System.out.println("资源已关闭");
        }
        System.out.println("=== SQL更新执行完成 ===");
        return i;
    }

    /*
    查询
     */
    public synchronized static <E> List<Map<String, Object>> jdbcBySelect(String sql, E... e) {
        List<Map<String, Object>> list = new ArrayList<>();
        getConnection();
        ResultSetMetaData rsmd = null;//当前Result中列相关的信息
        try {
            //获取Statement对象
            //执行查询的sql语句并接收结果集
            preparedStatement = conn.prepareStatement(sql);
            for (int i = 0; i < e.length; i++) {
                preparedStatement.setObject(i + 1, e[i]);
            }
            rest = preparedStatement.executeQuery();
            rsmd = rest.getMetaData();//获取ResultSetMetaData的对象
            int colunmCount = rsmd.getColumnCount();
            while (rest.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= colunmCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object columnValue = rest.getObject(i);
                    map.put(columnName, columnValue);
                }
                list.add(map);
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        } finally {
            closeResource();
        }
        return list;
    }


}
